Introduction

By now you probably know how to fetch data, store it in a database, and then display it in an app. Great! But keep in mind that when a large amount of data is displayed, users will often be looking for something specific, and won’t want to read through everything to find it. This is why it’s important to implement data filtering functionality, which you’ll recap in this reading.

For more detailed information about filtering data in SQL, please review the following lesson items from the course Working with Data in React Native:

• SELECT statement in Module 3, Lesson 3

• Connecting SQLite to State in Module 4, Lesson 2

Overview of Filtering in SQL

Filtering data in SQL is essential because it allows you to selectively retrieve only the rows that meet certain criteria from a database table. This is useful in many situations, such as when you want to find specific information or when you want to analyze data in a specific way. For example, you might use filtering to find all rows in a table where the salary column is greater than a certain amount, or to find all rows where the name column contains a specific string.

Filtering is an essential part of many SQL queries, and it is used to narrow down the results of a query to only the rows that are relevant for a particular purpose. Without the ability to filter data, it would be difficult to retrieve only the information you need from a large database table. This would make it much harder to analyze and understand the data, and it would also make it more difficult to perform tasks such as updating or deleting specific rows.

Overall, filtering is an important tool for working with data in SQL and is an essential part of many database tasks.

To create queries that perform filtering in a SQLite database, you can use the WHERE clause in a SELECT statement. The WHERE clause allows you to specify a condition that must be met for a row to be included in the results of the query.

Common Filtering Methods in SQL

The following query demonstrates the basic syntax for using the WHERE clause in a SELECT statement:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

For example, imagine you are working with a company database that contains a table which stores all employees.

To select all rows from the ‘ employees ’ table where the salary column is greater than 50000 , you can use the following query:

SELECT *
FROM employees
WHERE salary > 50000;

Another option is to use multiple conditions in the WHERE clause by using logical operators such as AND and OR . For example, to select all rows from the ‘ employees ’ table where the salary is greater than 50000 and the department is sales , you can use the following query:

SELECT *
FROM employees
WHERE salary > 50000 AND department = 'sales';

You can also use the IN operator to check if a value is contained in a list of values. For example, to select all rows from the ‘ employees ’ table where the department is either sales or marketing , you can use the following query:

SELECT *
FROM employees
WHERE department IN ('sales', 'marketing');

Finally, you can use the LIKE operator to perform pattern matching in the WHERE clause. The LIKE operator is often used with the % wildcard, which matches any number of characters. For example, to select all rows from the ‘ employees ’ table where the name column contains the string john , you can use the following query:

SELECT *
FROM employees
WHERE name LIKE '%john%';

Conclusion

In this reading, you reviewed the importance of filtering in SQLite databases and examined some query examples that illustrated the different operators you can leverage to retrieve the data you wish.